package com.yun.util.clickHouse.base;

import com.mchange.v2.c3p0.impl.NewProxyPreparedStatement;
import com.yun.util.clickHouse.CreateClickHouseTableConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ClickHouseJDBC {

    final Logger logger = LoggerFactory.getLogger(ClickHouseJDBC.class);

    private DataSource dataSource;
    private Connection connection ;
    private NewProxyPreparedStatement statement;
    private ResultSet rs;


    public ClickHouseJDBC(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    static Logger log = LoggerFactory.getLogger(CreateClickHouseTableConfig.class);


    private void getConnection() throws SQLException {
        this.connection = dataSource.getConnection();
    }

    public Connection getConn() throws SQLException {
        this.connection = dataSource.getConnection();
        return connection;
    }

    public void setParamExecuteSql(String sql,ArrayList<?> parms,ArrayList<Class<?>> typeList) throws SQLException {
        getConnection();
        statement = (NewProxyPreparedStatement)connection.prepareStatement(sql);
        for (int i = 0; i < parms.size(); i++) {
            Class<?> type = typeList.get(i);
            if (type.isAssignableFrom(long.class)||type.isAssignableFrom(Long.class)||type.isAssignableFrom(int.class)||type.isAssignableFrom(Byte.class)||type.isAssignableFrom(Integer.class)||type.isAssignableFrom(short.class))
                statement.setLong(i+1,parms.get(i) == null ? null : Long.valueOf(parms.get(i).toString()));
            if (type.isAssignableFrom(String.class))
                statement.setString(i+1,parms.get(i) == null ? null : parms.get(i).toString());
            if (type.isAssignableFrom(Double.class)||type.isAssignableFrom(Float.class))
                statement.setDouble(i+1,parms.get(i) == null ? null : Double.valueOf(parms.get(i).toString()));
            if (type.isAssignableFrom(Date.class))
                statement.setDate(i+1,(java.sql.Date) parms.get(i));
            if (type.isAssignableFrom(Double.class)||type.isAssignableFrom(Float.class)||type.isAssignableFrom(float.class)||type.isAssignableFrom(double.class))
                statement.setDouble(i+1,parms.get(i) == null ? null : Double.valueOf(parms.get(i).toString()));
            if (type.isAssignableFrom(Timestamp.class))
                statement.setTimestamp(i+1,(Timestamp) parms.get(i));
        }
        log.debug("ClickHouse SQL: {}",sql);
        rs=statement.executeQuery();
        close();
    }

    public void batchExecuteSql(String sqlMoudel,List<List> fieldValueList,List<List<Class<?>>> fieldTypeList){
        try {
            getConnection();
            statement = (NewProxyPreparedStatement)connection.prepareStatement(sqlMoudel);
            for (int i = 0; i < fieldValueList.size(); i++) {
                for (int j = 0; j < fieldValueList.get(i).size(); j++) {
                    Class<?> type = fieldTypeList.get(i).get(j);
                    if (type.isAssignableFrom(long.class)||type.isAssignableFrom(Long.class)||type.isAssignableFrom(int.class)||type.isAssignableFrom(Byte.class)||type.isAssignableFrom(Integer.class)||type.isAssignableFrom(short.class))
                        statement.setLong(j+1,fieldValueList.get(i).get(j) == null ? null : Long.valueOf(fieldValueList.get(i).get(j).toString()));
                    if (type.isAssignableFrom(String.class))
                        statement.setString(j+1,fieldValueList.get(i).get(j) == null ? null : fieldValueList.get(i).get(j).toString());
                    if (type.isAssignableFrom(Date.class))
                        statement.setDate(j+1,(java.sql.Date) fieldValueList.get(i).get(j));
                    if (type.isAssignableFrom(Double.class)||type.isAssignableFrom(Float.class)||type.isAssignableFrom(float.class)||type.isAssignableFrom(double.class))
                        statement.setDouble(j+1,fieldValueList.get(i).get(j) == null ? null : Double.valueOf(fieldValueList.get(i).get(j).toString()));
                    if (type.isAssignableFrom(Timestamp.class))
                        statement.setTimestamp(j+1,(Timestamp) fieldValueList.get(i).get(j));
                }
                statement.addBatch();
            }
            log.debug("ClickHouse SQL: {}",sqlMoudel);
            statement.executeBatch();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public ResultSet executeSql(String sql) throws SQLException{
        getConnection();
        log.debug("ClickHouse SQL: {}",sql);
        statement = (NewProxyPreparedStatement)connection.prepareStatement(sql);
        rs = statement.executeQuery(sql);
        return rs;
    }

    public void deleteSql(String sql) throws SQLException{
        getConnection();
        statement = (NewProxyPreparedStatement)connection.prepareStatement(sql);
        log.debug("ClickHouse SQL: {}",sql);
        rs=statement.executeQuery();
    }

    public void close()  {
        try {
            if(rs!=null){
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }

        }catch (SQLException e){
            logger.error(e.getMessage(),e);
        }

    }

}
